IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveEMailTemplate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SaveEMailTemplate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
/***************************************************************************    
* Name:   SaveEMailTemplate    
* Purpose:  Insert/Update a COIEMailTemplate    
*    
* PARAMETERS    
* Name    Description         
* -------------   -----------------------------------------------------------    
* @id    Unique id ( 0 for new records to be inserted)    
* @type    Type of the email template    
* @bodytext   body of the email   
* @subject Subject of the email
****************************************************************************/    
CREATE PROCEDURE [dbo].[SaveEMailTemplate] (    
    
@id INT,    
@type VARCHAR(50),    
@bodyText ntext,    
@moduleType VARCHAR(256),
@subject varchar(2000),
@to varchar(100),
@from varchar(100)
)    
    
AS    
    
BEGIN    
 SELECT @id = id FROM EMailTemplate WHERE type = @type    
 IF @id = 0    
 BEGIN    
    
  INSERT INTO EMailTemplate(     
      [Type],     
      BodyText,    
      ModuleType,
      [Subject],
      [To],
      [From]
     )    
     VALUES    
     (     
      @type,    
      @bodyText,    
      @moduleType,
      @subject,
      @to,
      @from
     )    
      
  IF @@ERROR <> 0 GOTO ERROR_HANDLER    
      
  SELECT @id = IDENT_CURRENT('EMailTemplate')    
    
  SELECT @id    
    
      
  RETURN     
 END    
 ELSE    
 BEGIN    
      
  UPDATE EMailTemplate    
  SET    
   [Type] = @type,    
   BodyText = @bodyText,    
   ModuleType = @moduleType,
   [Subject] = @subject,
   [To] = @to,
   [From] = @from
  WHERE [Id] = @id    
      
  IF @@ERROR <> 0 GOTO ERROR_HANDLER    
    
  SELECT @id     
  RETURN    
      
 END    
    
 ERROR_HANDLER:    
  IF @@ERROR <> 0     
  BEGIN    
   RAISERROR('Unable to Save E-Mail Template', 16, 1)    
   RETURN    
  END    
END    